var queues = require('mysql-queues');
const DEBUG = true;
var select = function(connection, callback) {
	var sql = 'select * from performance';
	connection.query(sql, function(err, rows) {
		if (err){
			console.log(err);
			console.log(err.stack);
		}
		callback(rows);
		connection.release();
	});
},
	add = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'insert into performance(assessmentPersonId,departmentId,perfDate,state,level,score,employeeId,preDeptId,lastChange,comment) values(?,?,?,?,?,?,?,?,?,?)';
		var trans = connection.startTransaction(); 
		trans.query(sql, [data.assessmentPersonId, data.departmentId, data.perfDate, data.state, data.level, data.score, data.employeeId, data.preDeptId, data.lastChange, data.comment], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					// console.log(info);
				});
			}
			callback(rows);
		});
		trans.execute();
		console.log('execute addUserRole');
		connection.release();
	},

	//插入绩效信息及绩效项信息
	insertPerf = function(connection, perData, basicItems, callback) {
		queues(connection, DEBUG);
		var sql1 = "insert into performance(departmentId,perfDate,state,employeeId,level,score,lastChange,preDeptId,project) values(?,?,?,?,?,?,?,?,?)";
        var sql2 = "insert into performance_term(term,content,weight,assPersonId,state,type,performanceId) values(?,?,?,?,?,?,?)"

        var trans = connection.startTransaction();
		trans.query(sql1,[perData.departmentId, perData.perfDate, perData.state, perData.employeeId, perData.level, perData.score, perData.lastChange, perData.preDeptId, perData.project], function(err, info) {
			if(err) {
				trans.rollback();
			}else{
				
		        for(var i = 0; i < basicItems.length; i++) {
		        	var item = basicItems[i];
		        	// console.log("item = ", item);
		        	trans.query(sql2, [item.term, item.content, item.weight,item.assPersonId,item.state, 0, info.insertId], function(err) {
		        		if(err){
		        			trans.rollback();
		        		}
		        	});
		        }
		        trans.commit();
			}
			
			callback(info.insertId);
		});
		trans.execute();
		connection.release();
		
	},
	updateAssPersonId = function(connection,performanceId){
		var sql = "select p.assPersonId from (select assPersonId,sum(weight) as weight from performance_term where performanceId="+performanceId+" group by assPersonId)p where p.weight=(select max(p.weight) from (select assPersonId,sum(weight) as weight from performance_term where performanceId="+performanceId+" group by assPersonId)p)";
		var trans = connection.startTransaction();
		trans.query(sql,function(err, info) {
			if(err) {
				trans.rollback();
			}else{
				// console.info("info");
				// console.info(info[0].assPersonId);
				var sql2 = "update performance set performance.assessmentPersonId='"+info[0].assPersonId+"' where performance.id='"+performanceId+"'";
						
				trans.query(sql2,function(err, info) {
					if(err) {
						trans.rollback();
					}
			        trans.commit();
				});
		    }
			trans.commit();
		});
		trans.execute();
		connection.release();
	},
	//更新绩效及绩效项信息
	updatePerf = function(connection, basicItems, updateItems, deleteItems, perData, callback) {
		queues(connection, DEBUG);
		// console.log("perData----:", perData);
		//绩效表更新语句
		// var sql1 = "update performance set assessmentPersonId=?,departmentId=?,perfDate=?,state=?,level=?,score=?,employeeId=?,lastChange=?,comment=? where id=?";
		var sql1 = "update performance set departmentId=?,perfDate=?,state=?,level=?,score=?,employeeId=?,lastChange=?,preDeptId = ?, project = ? where id=?";
		//绩效项表更新语句
		// var sql2 = "update performance_term set term=?,content=?,weight=?,completion=?,score=?,type=?,performanceId=? where id=?";
		var sql2 = "update performance_term set term=?,content=?,weight=?,completion=?,score=?,type=?,performanceId=?, assPersonId=?,state=? where id=?";
		//绩效项表插入语句
		var sql3 = "insert into performance_term(term,content,weight,type,performanceId,completion,score, assPersonId, state) values(?,?,?,?,?,?,?,?,?)";
		//绩效项表删除语句
		var sql4 = "delete from performance_term where id = ?";

		var trans = connection.startTransaction();
		// console.log("trans-------" , trans);
		trans.query(sql1,[perData.departmentId,perData.perfDate, perData.state, perData.level, perData.score, perData.employeeId,  perData.lastChange, perData.preDeptId, perData.project, perData.perfId], function(err, info) {
			if(err) {
				trans.rollback();
				console.log("绩效表更新失败！");
				// callback("绩效信息保存失败！");
			}else{
				if(updateItems != null && updateItems.length != 0) {
					for(var i = 0; i < updateItems.length; i++) {
						var updateTerm = updateItems[i];
						trans.query(sql2,[updateTerm.term, updateTerm.content, updateTerm.weight, updateTerm.completion, updateTerm.score, updateTerm.type, perData.perfId, updateTerm.assPersonId, updateTerm.state , updateTerm.termId], function(err, result) {
							if(err) {
								trans.rollback();
								console.log("绩效项表更新失败！");
								// callback("绩效信息保存失败！");
							}
						});
					}

				}
				if(basicItems != null && basicItems.length !=0) {
					for(var k = 0; k < basicItems.length; k++) {
						var item =basicItems[k];
						trans.query(sql3, [item.term, item.content, item.weight, item.type, perData.perfId, item.completion, item.score, item.assPersonId, item.state], function(err, rows) {
							if(err) {
								trans.rollback();
								console.log("绩效项表插入失败！");
								// callback("绩效信息保存失败！");
							}
						});
					}
					
				}
				if(deleteItems != null && deleteItems.length != 0) {
					for(n = 0; n < deleteItems.length; n++) {
						var delItem = deleteItems[n];
						trans.query(sql4, delItem, function(err,delResult) {
							if(err) {
								trans.rollback();
								console.log("绩效项表删除失败！");
							}
						}) ;
					}
				}
				trans.commit();
			}
			callback(info);
			
		});

        trans.execute();
        connection.release();
	},

	//更新绩效表的状态、成绩及等级
	updPerf = function(connection, data, callback) {
		
		queues(connection, DEBUG);
		var sql = "update performance set level=?,score=?,state=? where id=?";
		var trans = connection.startTransaction();
		trans.query(sql, [data.level, data.score, data.state, data.perfId], function(err, info) {
			
			if(err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			}else{
				trans.commit();
			}
			callback(info);
		});
		trans.execute();
		connection.release();

	},

	selPerByEmp = function(connection, employeeId, year,callback) {
		var sql = "select * from performance where employeeId=? and perfDate like ?";
		connection.query(sql, [employeeId, year+'%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});
	},
	selEmpPerfMonth = function(connection, employeeId, time, callback) {
		// console.log("time =" + time);

		var sql = "select p2.*, e2.name empName from (select p.*, e.name assname from performance p left join employee e on p.assessmentPersonId = e.id)p2 left join employee e2 on p2.employeeId = e2.id where p2.perfDate like ? and p2.employeeId = ?";
        connection.query(sql, [time+'%', employeeId], function(err, rows) {
        	if(err) {
        		console.log(err);
        		console.log(err.stack);
        		callback(null);
        	}else{
        		// console.log("selEmpPerfMonth---rows:", rows);
        		callback(rows);
        	}
        	connection.release();
        })
	},

	//根据审核人查询当月员工绩效列表
	selMonthPerfsByAss = function(connection, employeeId, time, stateKey, callback) {
		// var sql = "select p2.*, e2.name empName from (select p.*, e.name assName from performance p left join employee e on p.assessmentPersonId = e.id where p.assessmentPersonId = ?)p2 left join employee e2 on p2.employeeId = e2.id";
		// var sql = "select p3.*, o2.orgName projectDept from (select p2.*, o.orgName from (select p.*, e.name empName from performance p left join employee e on p.employeeId = e.id where p.assessmentPersonId = ?)p2 left join organization o on p2.departmentId = o.id)p3 left join organization o2 on p3.preDeptId = o2.id where p3.perfDate like ? order by p3.departmentId";
		var sqlStr = "";
		if(stateKey == "未审核") {
			sqlStr = sqlStr + " and p5.termState = 0";
		}else if(stateKey == "已审核") {
			sqlStr = sqlStr + " and p5.termState = 1";
		}
		var sql = "select * from (select p4.*, o2.orgName projectDept from (select p3.*, o.orgName from (select p2.*, e.name empName from (select distinct p.*,t.state termState from performance p left join performance_term t on p.id = t.performanceId where t.assPersonId = ? and p.state <>2 group by t.performanceId)p2 left join employee e on p2.employeeId = e.id)p3 left join organization o on p3.departmentId = o.id)p4 left join organization o2 on p4.preDeptId = o2.id where p4.perfDate like ?)p5 where 1=1 " + sqlStr +" order by p5.departmentId";
		connection.query(sql, [employeeId, '%' + time + '%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	//根据审核人查询员工绩效列表
	selPerfsByAss = function(connection, employeeId, keyword, time, callback) {
		// var sql = "select p4.* from (select p3.* from (select p5.*, o2.orgName projectDept from (select p2.*, o.orgName from (select p.*, e.name empName from performance p left join employee e on p.employeeId = e.id where p.assessmentPersonId = ?)p2 left join organization o on p2.departmentId = o.id)p5 left join organization o2 on p5.preDeptId = o2.id)p3 where p3.empName like ? or p3.orgName like ? or p3.projectDept like ?)p4 where p4.perfDate like ? and p4.state = 1 order by p4.perfDate desc";
		var sql = "select p6.* from (select * from (select p4.*, o2.orgName projectDept from (select p3.*, o.orgName from (select p2.*, e.name empName from (select distinct p.* from performance p left join performance_term t on p.id = t.performanceId where t.assPersonId = "+employeeId+" and t.state = 1 and p.id in (select a1.performanceId from (select count(*)countNum, a.performanceId from performance_term a where a.assPersonId = "+employeeId+" group by a.performanceId)a1 left join (select sum(b.state)num, b.performanceId pid from performance_term b where b.assPersonId = "+employeeId+" group by b.performanceId)b1 on a1.performanceId = b1.pid where a1.countNum = b1.num))p2 left join employee e on p2.employeeId = e.id)p3 left join organization o on p3.departmentId = o.id)p4 left join organization o2 on p4.preDeptId = o2.id )p5 where p5.empName like ? or p5.orgName like ? or p5.projectDept like ?)p6 where p6.perfDate like ? order by p6.perfDate desc";
		connection.query(sql, [ '%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%', '%' + time + '%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	//根据被考核人及考核周期查询绩效
	selPerfByEmpDate = function(connection, empId, perfDate, callback) {
		var sql = "select * from performance where employeeId = ? and perfDate like ?";
		connection.query(sql, [empId, '%' + perfDate + '%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
				
			callback(rows);
			connection.release();
		});
	},
	selDetailByEmpDate = function(connection, empId, perfDate, callback) {
		var sql = "select p2.*, e2.name empName from (select p.*, e.name assName from performance p left join employee e on p.assessmentPersonId = e.id where p.employeeId=? and p.perfDate like ?)p2 left join employee e2 on p2.employeeId = e2.id";
		connection.query(sql, [empId, '%' + perfDate + '%'], function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
				
			callback(rows);
			connection.release();
		});
	},

	delet = function(connection, id, callback) {
		queues(connection, DEBUG);
		var sql = 'delete from performance where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, id, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log(info);
				});
			}
			// console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		// console.log('execute');
		connection.release();
	},
	update = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'update performance set assessmentPersonId=?,departmentId=?,perfDate=?,state=?,level=?,score=?,employeeId=?,preDeptId=?,lastChange=?,comment=? where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, [data.assessmentPersonId, data.departmentId, data.perfDate, data.state, data.level, data.score, data.employeeId, data.preDeptId, data.lastChange, data.comment, data.id], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					// console.log('info:', info);
				});
			}
			// console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		// console.log('execute update');
		connection.release();
	},

	//根据员工id查询历史绩效成绩
	selEmpHisPerf = function(connection, empId, callback) {
		var sql = "select * from performance where employeeId = ? and state = 2  order by perfDate desc";
		connection.query(sql, empId, function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	//根据绩效id查询绩效成绩信息
	findPerfById = function(connection, id, callback) {
		var sql = "select p2.*, e2.name empName from (select p.*, e.name assName from performance p left join employee e on p.assessmentPersonId = e.id where p.id=?)p2 left join employee e2 on p2.employeeId = e2.id";

		connection.query(sql, id, function(err, rows) {
			if(err){
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	findById = function(connection, id, callback) {
		var sql = 'select * from performance where id=?';
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},

	checkAssignmentIdExit = function(connection, data, callback){
		var sql = 'select * from performance where state="0" and assessmentPersonId=? and (perfDate=? or perfDate=?)';
		connection.query(sql, [data.id, data.date, data.date_next], function(err,rows){
			if(err){
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	changeState = function(connection, data, callback){
		queues(connection, DEBUG);
		//var termIds = data.idAll;
		var sqlPer = 'update performance set state=2 where id=?';
		var sql2 = "update performance set state = 2, comment = ? where id=?";
		//var sqlPerTerm = 'update performance_term set state=2 where id=?';
		var ids = data.ids;
		var comment = data.comment;
		var trans = connection.startTransaction();
		var flag = false;
		if(comment != undefined) {
			for(var i = 0; i < ids.length; i ++){
			    trans.query(sql2, [comment,ids[i]], function(err, rows){
				    if (err){
					    flag = true;
				    }
			    });
		}
		}else{
			for(var i = 0; i < ids.length; i ++){
				trans.query(sqlPer, ids[i], function(err, rows){
					if (err){
						flag = true;
					}
				});
			}
		}
		// for(var i = 0; i < ids.length; i ++){
		// 	trans.query(sqlPer, ids[i], function(err, rows){
		// 		if (err){
		// 			flag = true;
		// 		}
		// 	});
		// }
		if(flag){
			trans.rollback();
			callback('确认失败！');
		} else {
			trans.commit();
			callback('确认成功！');
		}
		trans.execute();
		connection.release();
	},

	returnPerf = function(connection, data, callback){
		queues(connection, DEBUG);
		var sqlPer = 'update performance set state=3,comment=? where id=?';
		var sqlPerTerm = 'update performance_term set state=0 where id=?';
		var ids = data.termId; //绩效项数组
		var trans = connection.startTransaction();
		trans.query(sqlPer, [data.reason, data.id], function(err, rows){ 
			if(err){
				console.log(err);
				console.log(err.stack);
			}else{
				var flag = false;
				for(var i = 0; i < ids.length; i ++){
					trans.query(sqlPerTerm, ids[i] * 1, function(err, rows){
						if(err){
							flag = true;
						}
					});
				}
				if(flag){
					trans.rollback();
					callback('退回失败');
				} else{
					trans.commit();
					callback('退回成功');
				}
			}
		});
		trans.execute();
		connection.release();
	},
	//根据员工id及时间修改绩效表中的员工部门
	updEmpPerf = function(connection, empId, time, deptId) {
		queues(connection,DEBUG);
		var sql = "update performance p set p.departmentId = ? where p.employeeId = ? and p.perfDate like ?";
        var trans = connection.startTransaction();
        trans.query(sql, [deptId, empId, '%' + time + '%'],  function(err) {
        	if(err) {
        		console.log(err);
        		console.log(err.stack);
        		trans.rollback();
        	}else{
        		trans.commit();
        	}
        	trans.execute();
        	connection.release();
        });
	},

	changeWeight = function(connection,data,callback){
		queues(connection,DEBUG);
		var sql = 'update performance_term set weight=? where id=?';
		var trans = connection.startTransaction();
		var flag = false;
		
		for(var i = 0; i < data.length; i ++){
			trans.query(sql, [data[i].weight, data[i].id], function(err, rows){
				if(err){
					flag = true;
				}
			});
		}
		if(flag){
			trans.rollback();
			callback('保存失败！');
		} else{
			trans.commit();
			callback('保存成功！');
		}
		trans.execute();
		connection.release();
	};

module.exports = {
	selectPerformance: select,
	addPerformance: add,
	deletePerformance: delet,
	updatePerformance: update,
	findById: findById,
	insertPerf: insertPerf,
	selPerByEmp: selPerByEmp,
	selEmpPerfMonth: selEmpPerfMonth,
	updatePerf: updatePerf,
	selMonthPerfsByAss: selMonthPerfsByAss,
	selPerfsByAss: selPerfsByAss,
	findPerfById: findPerfById,
	selEmpHisPerf: selEmpHisPerf,
	selPerfByEmpDate: selPerfByEmpDate,
	checkAssignmentIdExit: checkAssignmentIdExit,
	changeState: changeState,
	returnPerf: returnPerf,
	updateAssPersonId: updateAssPersonId,
	updPerf: updPerf,
	changeWeight: changeWeight,
	updEmpPerf: updEmpPerf,
	selDetailByEmpDate: selDetailByEmpDate
};